﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
///JGGL 的摘要说明
/// </summary>
public class JGGL
{
  public JGGL()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }

  public static SqlDataReader GetXX(string sID)
  {
    string sSql = "select *,convert(varchar,jgrq,23) rq from jcxx_yqjg where jgid=@0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sID;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    return dr;
  }

  //判断此名称是否存在
  public static bool ExistID(string sRQ, string sYQXZ)
  {
    bool flag = false;
    string sSql = "select jgid as num from jcxx_yqjg where jgrq>=@0 and yqxzid=@1";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = sRQ;
    cmdParams[1].Value = sYQXZ;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.HasRows)
    {
      flag = true;
    }
    dr.Close();
    return flag;
  }

  public static DataSet GetDS()
  {
    string sSql = "select ROW_NUMBER() OVER (ORDER BY jgrq desc) AS xh,a.*,convert(varchar,jgrq,23) RQ,b.YQXZ from jcxx_yqjg a,jcxx_yqxz b where a.yqxzid=b.yqxzid order by rq desc";
    DataSet ds = DbHelperSQL.Query(sSql);
    return ds;
  }
  public static DataTable GetJG(string sYQXZ, string sRQ)
  {
    string sSql = @"select jg,jj from jcxx_yqjg where jgrq=(select max(jgrq) from jcxx_yqjg where yqxzid=@0 and jgrq<=@1) and yqxzid=@0";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
    cmdParams[0].Value = sYQXZ;
    cmdParams[1].Value = sRQ;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds.Tables[0];
  }
  public static string[] GetJGJJ(string sKHID, string sDate)
  {
    string[] jg = new string[3] { "", "", "" };
    string sSql = @"select jg,jj,yqxz from jcxx_yqjg a,jcxx_khyqxz b,jcxx_yqxz c
where a.yqxzid=b.yqxzid and b.khid=@0 and c.yqxzid=a.yqxzid and jgrq=(select max(jgrq) from jcxx_yqjg where yqxzid=b.yqxzid and jgrq<=@1)";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
    cmdParams[0].Value = sKHID;
    cmdParams[1].Value = sDate;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    if (dr.Read())
    {
      jg[0] = dr["jg"].ToString();
      jg[1] = dr["jj"].ToString();
      jg[2] = dr["yqxz"].ToString();
    }
    dr.Close();
    return jg;
  }

  public static DataTable Get_YQJG(string sYQXZ, string sRQ)
  {
    string sSql = @"select * from v_yqxz_jg where yqxzid=@0 and jgrq<=@1 order by yqxzid,jgrq desc";
    SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
    cmdParams[0].Value = sYQXZ;
    cmdParams[1].Value = sRQ;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds.Tables[0];
  }
}
